package com.taskcollection.service;

import com.taskcollection.bean.StudentBean;
import com.taskcollection.bean.TeacherBean;
import com.taskcollection.util.DBConnection;
import com.taskcollection.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by haocongshun on 2016/7/20.
 */
public class ServiceUtil {
    public static boolean checkPhone(String tableinfo,String phone){
        int count = 0;
        Connection conn = DBConnection.getinstance().getConnection();
        String sql = "";
        if(tableinfo.equals("teacherinfo")){
            sql = "SELECT COUNT(*) as flag FROM teacherinfo WHERE teacher_phone=?";
        }else if(tableinfo.equals("studentinfo")){
            sql = "SELECT COUNT(*) as flag FROM studentinfo WHERE student_phone=?";
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,phone);
            rs = ps.executeQuery();
            System.out.println(tableinfo+"**"+phone+"**"+sql);
            if (rs.next()) {
                count = rs.getInt("flag");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            DBUtil.closeConnection(conn);
        }
        return count > 0 ? true : false;
    }
    public static boolean reg(TeacherBean tea){
        String sql = "insert INTO teacherinfo(teacher_id,teacher_name,teacher_phone,teacher_passwd) values(?,?,?,?)";
        String id = ServiceUtil.getPK("teacher_id","teacherinfo");
        return regHandle(sql,id,tea.getTeacher_name(),tea.getTeacher_phone(),tea.getTeacher_passwd());
    }
    public static boolean reg( StudentBean stu){
        System.out.println("进入ServiceUtil中的regStudent");
        String sql = "insert INTO studentinfo(student_id,student_name,student_phone,student_passwd) values(?,?,?,?)";
        String id = ServiceUtil.getPK("student_id","studentinfo");
        return regHandle(sql,id,stu.getStudent_name(),stu.getStudent_phone(),stu.getStudent_passwd());
    }
    public static boolean regHandle(String sql,String id,String name,String phone,String passwd){
        Connection conn = DBConnection.getinstance().getConnection();
        System.out.println("进入ServiceUtil中的HandleReg");
        PreparedStatement ps = null;
        boolean flag = false;
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);
            ps.setString(2,name);
            ps.setString(3,phone);
            ps.setString(4,passwd);
            flag = ps.execute();
            System.out.println(sql+"执行插入sql");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePreparedStatement(ps);
            DBUtil.closeConnection(conn);
        }
        return flag;
    }
    public static String getPK(String PK,String table){
        String sql = "SELECT MAX("+PK+") as a FROM "+table;
        Connection conn = DBConnection.getinstance().getConnection();
        PreparedStatement ps =null;
        ResultSet rs = null;
        String result ="";
        try{
            ps = conn.prepareStatement(sql);
            System.out.println(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                if(rs.getString("a")==null){
                    result="0";
                }else {
                    System.out.println(Integer.parseInt(rs.getString("a")));
                    System.out.println(Integer.parseInt(rs.getString("a"))+1);
                    result=String.valueOf(Integer.parseInt(rs.getString("a"))+1);
                }
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            DBUtil.closeConnection(conn);
        }
        return null;
    }
//    public static Object getById(String id,String id_column,String table,Object obj){
//        String sql = "select * from "+table+"where "+id_column+"=?";
//        Connection conn = DBConnection.getinstance().getConnection();
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        try{
//            ps=conn.prepareStatement(sql);
//            ps.setString(1,id);
//            rs = ps.executeQuery();
//            if(rs.next()){
//                rs.
//            }
//
//        }
//    }
}
